﻿CREATE VIEW [dbo].[BAM_270_271]
AS 
--This correlates 270 and 271 pairs on the BHT03 and TRN02 Identifiers
--Performance can be improved by using CTE
	
select 
	 BTSMessageID
	,BTSInterchangeID
	,MessageID, 'TransactionStatus' = 
     CASE
			WHEN 
				(select 
					count(*) 
				from 
					dbo.BAM_271 b271 
				where 
					b271.BHT03_SubmitterTransactionIDentifier = b270.BHT03_SubmitterTransactionIDentifier and b270.Subscriber_TRN02_TraceNumber = b271.Subscriber_TRN02_TraceNumber) = 1 
				THEN 
					'Completed'
				WHEN 
					(select 
						count(*) 
					from 
						dbo.BAM_271 b271 
					where b271.BHT03_SubmitterTransactionIDentifier = b270.BHT03_SubmitterTransactionIDentifier and b270.Subscriber_TRN02_TraceNumber = b271.Subscriber_TRN02_TraceNumber) > 1 
				THEN 
					'Duplicate BHT03 ID'
				ELSE 
					'Incomplete'
				END
				
				,(Select top 1 
					BTSMessageID 
				from 
					dbo.BAM_271 b271 
				where 
					b271.BHT03_SubmitterTransactionIDentifier = b270.BHT03_SubmitterTransactionIDentifier and b270.Subscriber_TRN02_TraceNumber = b271.Subscriber_TRN02_TraceNumber) 
				as 
					'BTSMessageID_271'
				,(Select top 1 
					BTSInterchangeID 
				from 
					dbo.BAM_271 b271 
				where 
					b271.BHT03_SubmitterTransactionIDentifier = b270.BHT03_SubmitterTransactionIDentifier and b270.Subscriber_TRN02_TraceNumber = b271.Subscriber_TRN02_TraceNumber) 
				as 
					'BTSInterchangeID_271'
from BAM_270 b270
;
--drop VIEW [dbo].[BAM_270_271]


